﻿// =====================================================
// Copyright (C) 2016-2022 Fanjia
// All rights reserved
// 机器名称: PC-LIWEI
// 创建时间: 2022/12/13 11:04:27
// 创 建 人: 李伟
// ======================================================
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WindowsFormsApplication1.Common;
using FanjiaHotelPlatform.utils.Kits;
using FanJiaCommon.Kits;
using JobBatDeal.SqlDbHelper;
using log4net;
using MySql.Data.MySqlClient;
using Quartz;
using Z.BulkOperations;
using JobBatDeal.Common;
using JobBatDeal.DbHelper;
using DbHelperMySQL = JobBatDeal.DbHelper.DbHelperMySQL;
using ConvertKit = JobBatDeal.Common.ConvertKit;
using SimilarityGenerator = JobBatDeal.Common.SimilarityGenerator;

namespace JobBatDeal.Jobs
{
    public class ReplaceToMeituanJob : IJob
    {
        private readonly ILog _logger = LogManager.GetLogger(typeof(HotelBasicInfoUptJob));
        private static string SupplierId = "B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC";

        public void Execute(IJobExecutionContext context)
        {
            _logger.InfoFormat("===***********HotelBasicInfoUptJob 执行开始*********===");

            //OnlyMeituanInfoUpt();

            //SaveMeituanId();
            //SaveMeituanId_ZP();

            //ReplaceToMeituanInfoByCity();
            //ToMeituanInfoZp();

            //ToMeituanInfoByCity(Guid.Parse("F9D2DD88-B2E6-478D-9DD8-907E0B85AA1A"));

            UpdateHotelInfoBat();

            _logger.InfoFormat("===***********HotelBasicInfoUptJob 执行完成*********===");
        }

        /// <summary>
        /// 美团普票酒店按城市处理
        /// </summary>
        public void ReplaceToMeituanInfoByCity()
        {
            var dtProv = DbHelperSQL.Query("SELECT Id,Name FROM Provinces").Tables[0];

            if (dtProv != null && dtProv.Rows.Count > 0)
            {
                foreach (DataRow drProv in dtProv.Rows)
                {
                    _logger.InfoFormat($">>>>开始处理省份：{drProv["Name"].ToString()}");

                    var dtCity = DbHelperSQL.Query($"SELECT Id,Name FROM Cities WHERE Isdeleted =0 AND ProvinceId='{drProv["Id"].ToString()}'").Tables[0];

                    if (dtCity != null && dtCity.Rows.Count > 0)
                    {
                        foreach (DataRow drCity in dtCity.Rows)
                        {
                            _logger.InfoFormat($"---->>开始处理城市：{drCity["Name"].ToString()}");

                            var cityId = Guid.Parse(drCity["Id"].ToString());
                            ToMeituanInfoByCity(cityId);

                            _logger.InfoFormat($"---->>城市处理完成：{drCity["Name"].ToString()}");
                        }
                    }

                    _logger.InfoFormat($">>>>省份：{drProv["Name"].ToString()} 结束>>>>");
                }
            }
        }

        /// <summary>
        /// 美团普票酒店替换，具体城市处理
        /// </summary>
        /// <param name="cityId"></param>
        public void ToMeituanInfoByCity(Guid cityId)
        {
            var strSql = $@"SELECT b.[Id], m.SupplierHotelId
                      ,[Name]
                      ,[Address]
                      ,[Phone]
                      ,[Longitude]
                      ,[Latitude]
                  FROM [HotelBasicInfoes] b,meituanhotel_mapping m
                  where b.CityId='{cityId}' and m.HotelId=b.Id and b.IsDeleted=0";

            try
            {
                var dtFjHotel = DbHelperSQL.Query(strSql).Tables[0];

                #region MyRegion
                //DbHelperMySQL.ExecuteSqlWithConn("Truncate Table meituan_id", "MysqlConn", "meituanhoteldata");

                //var mIdDt = DbHelperMySQL
                //    .Query("SELECT * FROM meituan_id WHERE 1=0", "MysqlConn", "meituanhoteldata").Tables[0];

                //if (dtFjHotel.Rows.Count > 0)
                //{
                //    foreach (DataRow dr in dtFjHotel.Rows)
                //    {
                //        var nRow = mIdDt.NewRow();
                //        nRow["HotelId"] = dr["SupplierHotelId"];
                //        mIdDt.Rows.Add(nRow);
                //    }

                //    SaveDataTableMySql(mIdDt, "meituan_id", "MysqlConn", "meituanhoteldata");
                //}

                //var dtMeituanHotel = DbHelperMySQL
                //    .Query("SELECT HotelId,HotelName,HotelDescript,AmapLatitude,AmapLongitude,Address,BusinessDistrict,AvgScore,Telephone,FrontImage,OpenYear,RenovationYear,RoomQuantity,StarRating,CheckInFrom,CheckOutTo,FanjiaFacilities,FanjiaKeywords FROM meituanhotelinfo WHERE HotelId IN(SELECT HotelId FROM meituan_id)", "MysqlConn", "meituanhoteldata").Tables[0];
                #endregion

                var dtMeituanHotel = DbHelperMySQL
                    .Query($@"SELECT HotelId,HotelName,HotelDescript,AmapLatitude,AmapLongitude,Address,BusinessDistrict,AvgScore,FloorNum,Telephone,FrontImage,OpenYear,RenovationYear,RoomQuantity,StarRating,CheckInFrom,CheckOutTo,FanjiaFacilities,FanjiaKeywords,LocationId
                        FROM meituanhotelinfo WHERE FanjiaCityId='{cityId}'", "MysqlConn", "meituanhoteldata").Tables[0];

                if (dtMeituanHotel != null && dtMeituanHotel.Rows.Count > 0)
                {
                    if (dtFjHotel.Rows.Count > 0)
                    {
                        var dtHotelNew = DbHelperSQL.Query("SELECT * FROM HotelBicInfo_Meituan WHERE 1=0").Tables[0];

                        var dicHotelId = new Dictionary<Guid, int>();

                        foreach (DataRow dr in dtFjHotel.Rows)
                        {
                            var hotelId = Guid.Parse(dr["Id"].ToString());
                            if (dicHotelId.ContainsKey(hotelId))
                                continue;

                            var supplierHotelId = dr["SupplierHotelId"].ToString();

                            var row = dtMeituanHotel.Select("HotelId=" + supplierHotelId);

                            JudgeRepalceInfo(row, dr, dtHotelNew, cityId);

                            dicHotelId.Add(hotelId, 1);
                        }

                        if (dtHotelNew.Rows.Count > 0)
                        {
                            SaveDataTableSqlServer(dtHotelNew, "HotelBicInfo_Meituan");
                        }
                    }
                }

                _logger.Info("OK!");
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        /// <summary>
        /// 美团专票酒店基础信息替换
        /// </summary>
        public void ToMeituanInfoZp()
        {
            var strSql = $@"SELECT b.[Id], m.SupplierHotelId
                      ,[Name],CityId
                      ,[Address]
                      ,[Phone]
                      ,[Longitude]
                      ,[Latitude]
                  FROM [HotelBasicInfoes] b,meituanhotel_mapping m
                  WHERE m.HotelId=b.Id and b.IsDeleted=0";

            SupplierId = "FFF60DB6-9B70-49D5-AF8B-8F2E6D2ED480";

            try
            {
                var dtFjHotel = DbHelperSQL.Query(strSql).Tables[0];
               
                var dtMeituanHotel = DbHelperMySQL
                    .Query($@"SELECT HotelId,HotelName,HotelDescript,AmapLatitude,AmapLongitude,Address,BusinessDistrict,AvgScore,FloorNum,Telephone,FrontImage,OpenYear,RenovationYear,RoomQuantity,StarRating,CheckInFrom,CheckOutTo,FanjiaFacilities,FanjiaKeywords,LocationId
                        FROM meituanhotelinfo", "MysqlConn", "meituanhoteldata_zp").Tables[0];

                if (dtMeituanHotel != null && dtMeituanHotel.Rows.Count > 0)
                {
                    if (dtFjHotel.Rows.Count > 0)
                    {
                        var dtHotelNew = DbHelperSQL.Query("SELECT * FROM HotelBicInfo_Meituan WHERE 1=0").Tables[0];

                        var dicHotelId = new Dictionary<Guid, int>();

                        foreach (DataRow dr in dtFjHotel.Rows)
                        {
                            var cityId = Guid.Parse(dr["CityId"].ToString());
                            var supplierHotelId = dr["SupplierHotelId"].ToString();

                            var row = dtMeituanHotel.Select("HotelId=" + supplierHotelId);

                            var hotelId = Guid.Parse(dr["Id"].ToString());
                            if (dicHotelId.ContainsKey(hotelId))
                                continue;

                            JudgeRepalceInfo(row, dr, dtHotelNew, cityId);

                            dicHotelId.Add(hotelId, 1);
                        }

                        if (dtHotelNew.Rows.Count > 0)
                        {
                            SaveDataTableSqlServer(dtHotelNew, "HotelBicInfo_Meituan");
                        }
                    }
                }

                _logger.Info("OK!");
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        private static void JudgeRepalceInfo(DataRow[] row, DataRow dr, DataTable dtHotelNew, Guid cityId)
        {
            if (row != null && row.Length > 0)
            {
                var fjName = dr["Name"].ToString();
                var fjPhone = dr["Phone"].ToString();
                var fjLat = ConvertKit.ToDecimal(dr["Latitude"]);
                var fjLngr = ConvertKit.ToDecimal(dr["Longitude"]);

                var mtName = row[0]["HotelName"].ToString();
                var mtPhone = row[0]["Telephone"].ToString();
                var mtLat = ConvertKit.ToDecimal(row[0]["AmapLatitude"]);
                var mtLngr = ConvertKit.ToDecimal(row[0]["AmapLongitude"]);

                var hotelNameSimilarity =
                    Common.SimilarityGenerator.GetNameSimilarity(fjName, mtName);
                var hotelPhoneSimilarity =
                    Common.SimilarityGenerator.GetPhoneSimilarity(fjPhone, mtPhone);

                var hotelCoordinateSimilarity = 0D;
                if (fjLat > 0 && fjLngr > 0 && mtLat > 0 && mtLngr > 0)
                {
                    hotelCoordinateSimilarity =
                        Common.SimilarityGenerator.GetCoordinateSimilarity(fjLat, fjLngr, mtLat, mtLngr);
                }

                var isSame = false;
                //同一供应商名称一致，则通过
                if (fjName == mtName)
                {
                    isSame = true;
                }
                //同一供应商名称不一致（名称相似度在90%以上）、电话一致、经纬度在200米范围内，则通过
                else if ((hotelNameSimilarity > 0.9D && hotelPhoneSimilarity == 1) ||
                         (hotelNameSimilarity >= 0.8D && hotelCoordinateSimilarity == 1) ||
                         (hotelNameSimilarity > 0.82D && hotelPhoneSimilarity == 1 &&
                          hotelCoordinateSimilarity >= 0.9D))
                {
                    isSame = true;
                }

                if (isSame)
                {
                    var drNew = dtHotelNew.NewRow();

                    drNew["Id"] = dr["Id"];
                    drNew["Name"] = row[0]["HotelName"];
                    drNew["StarRating"] = ConvertKit.ToInt32(row[0]["StarRating"], 1);
                    drNew["Address"] = row[0]["Address"];
                    drNew["EstablishmentDate"] = row[0]["OpenYear"];
                    drNew["RenovationDate"] = row[0]["RenovationYear"];
                    drNew["NumberOfRooms"] = row[0]["RoomQuantity"];
                    drNew["FloorHeight"] = row[0]["FloorNum"];
                    drNew["Phone"] = row[0]["Telephone"];
                    drNew["Introduction"] = row[0]["HotelDescript"];
                    drNew["Longitude"] = row[0]["AmapLongitude"];
                    drNew["Latitude"] = row[0]["AmapLatitude"];
                    drNew["CheckInTime"] = row[0]["CheckInFrom"];
                    drNew["CheckOutTime"] = row[0]["CheckOutTo"];
                    
                    drNew["CityId"] = cityId;
                    drNew["Keywords"] = row[0]["FanjiaKeywords"];
                    drNew["ThumbImageUrl"] = row[0]["FrontImage"];
                    drNew["StarCategory"] = 2;
                    drNew["SupplierId"] = SupplierId;

                    drNew["TotalScore"] = ConvertKit.ToDecimal(row[0]["AvgScore"], 0);

                    var districtCode = row[0]["LocationId"].ToString();
                    if (!string.IsNullOrEmpty(districtCode) && districtCode.Length == 6)
                    {
                        drNew["DistrictCode"] = districtCode;
                    }

                    var businessDistrictS = Convert.ToString(row[0]["BusinessDistrict"]);
                    if (!string.IsNullOrEmpty(businessDistrictS) && "无合适商圈" != businessDistrictS)
                    {
                        drNew["BusinessDistrict"] = businessDistrictS.Replace('/', ',');
                    }

                    var fanjiaFacilities = row[0]["FanjiaFacilities"].ToString();
                    if (!string.IsNullOrEmpty(fanjiaFacilities))
                    {
                        drNew["HotelFacilities"] = fanjiaFacilities;

                        var facList = JsonKit.DeserializeJsonToList<HotelFacilityCategoryKit>(fanjiaFacilities);

                        if (facList?.Count > 0)
                        {
                            var strFac = "";

                            facList.ForEach(c =>
                            {
                                if (c.Items?.Count > 0)
                                {
                                    c.Items.ForEach(i =>
                                    {
                                        strFac = $"{strFac},{i.Title}";
                                    });
                                }
                            });

                            drNew["Facilities"] = strFac.Trim(',');
                        }
                    }

                    dtHotelNew.Rows.Add(drNew);
                }
            }
        }

        private static void OnlyRepalceInfo(DataRow[] row, DataRow dr, DataTable dtHotelNew, Guid cityId)
        {
            if (row != null && row.Length > 0)
            {
                var fjName = dr["Name"].ToString();
                var fjPhone = dr["Phone"].ToString();
                var fjLat = ConvertKit.ToDecimal(dr["Latitude"]);
                var fjLngr = ConvertKit.ToDecimal(dr["Longitude"]);

                var mtName = row[0]["HotelName"].ToString();
                var mtPhone = row[0]["Telephone"].ToString();
                var mtLat = ConvertKit.ToDecimal(row[0]["AmapLatitude"]);
                var mtLngr = ConvertKit.ToDecimal(row[0]["AmapLongitude"]);

                var hotelNameSimilarity =
                    SimilarityGenerator.GetNameSimilarity(fjName, mtName);
                var hotelPhoneSimilarity =
                    SimilarityGenerator.GetPhoneSimilarity(fjPhone, mtPhone);

                var hotelCoordinateSimilarity = 0D;
                if (fjLat > 0 && fjLngr > 0 && mtLat > 0 && mtLngr > 0)
                {
                    hotelCoordinateSimilarity =
                        SimilarityGenerator.GetCoordinateSimilarity(fjLat, fjLngr, mtLat, mtLngr);
                }

                var replace = false;
                if (fjName != mtName || fjPhone != mtPhone || hotelCoordinateSimilarity < 1)
                {
                    replace = true;
                }

                if (replace)
                {
                    var drNew = dtHotelNew.NewRow();

                    drNew["Id"] = dr["Id"];
                    drNew["Name"] = row[0]["HotelName"];
                    drNew["StarRating"] = ConvertKit.ToInt32(row[0]["StarRating"], 1);
                    drNew["Address"] = row[0]["Address"];
                    drNew["EstablishmentDate"] = row[0]["OpenYear"];
                    drNew["RenovationDate"] = row[0]["RenovationYear"];
                    drNew["NumberOfRooms"] = row[0]["RoomQuantity"];
                    drNew["FloorHeight"] = row[0]["FloorNum"];
                    drNew["Phone"] = row[0]["Telephone"];
                    drNew["Introduction"] = row[0]["HotelDescript"];
                    drNew["Longitude"] = row[0]["AmapLongitude"];
                    drNew["Latitude"] = row[0]["AmapLatitude"];
                    drNew["CheckInTime"] = row[0]["CheckInFrom"];
                    drNew["CheckOutTime"] = row[0]["CheckOutTo"];

                    drNew["CityId"] = cityId;
                    drNew["Keywords"] = row[0]["FanjiaKeywords"];
                    drNew["ThumbImageUrl"] = row[0]["FrontImage"];
                    drNew["StarCategory"] = 2;
                    drNew["SupplierId"] = SupplierId;

                    drNew["TotalScore"] = ConvertKit.ToDecimal(row[0]["AvgScore"], 0);

                    var districtCode = row[0]["LocationId"].ToString();
                    if (!string.IsNullOrEmpty(districtCode) && districtCode.Length == 6)
                    {
                        drNew["DistrictCode"] = districtCode;
                    }

                    var businessDistrictS = Convert.ToString(row[0]["BusinessDistrict"]);
                    if (!string.IsNullOrEmpty(businessDistrictS) && "无合适商圈" != businessDistrictS)
                    {
                        drNew["BusinessDistrict"] = businessDistrictS.Replace('/', ',');
                    }

                    var fanjiaFacilities = row[0]["FanjiaFacilities"].ToString();
                    if (!string.IsNullOrEmpty(fanjiaFacilities))
                    {
                        drNew["HotelFacilities"] = fanjiaFacilities;

                        var facList = JsonKit.DeserializeJsonToList<HotelFacilityCategoryKit>(fanjiaFacilities);

                        if (facList?.Count > 0)
                        {
                            var strFac = "";

                            facList.ForEach(c =>
                            {
                                if (c.Items?.Count > 0)
                                {
                                    c.Items.ForEach(i =>
                                    {
                                        strFac = $"{strFac},{i.Title}";
                                    });
                                }
                            });

                            drNew["Facilities"] = strFac.Trim(',');
                        }
                    }

                    dtHotelNew.Rows.Add(drNew);
                }
            }
        }

        private void SaveDataTableSqlServer(DataTable dtSave, string tableName, string keyName = "Id")
        {
            try
            {
                var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                using (DbConnection connection = new SqlConnection(connectionString))
                {
                    dtSave.TableName = tableName; //"HotelBasicInfoes";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = keyName;
                    //bulk.BulkInsert(dt);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    bulk.BulkMerge(dtSave);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        private void UptDataTableSqlServer(DataTable dtSave, string tableName, string keyName = "Id")
        {
            try
            {
                var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
                using (DbConnection connection = new SqlConnection(connectionString))
                {
                    dtSave.TableName = tableName; //"HotelBasicInfoes";
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = keyName;
                    //bulk.BulkInsert(dt);
                    bulk.BulkUpdate(dtSave);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dtSave);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        public void SaveDataTableMySql(DataTable dtSave, string tableName, string connStr, string dbStr)
        {
            var mysqlConnStr = DbHelperMySQL.GetConnStr(connStr, dbStr);

            try
            {
                using (DbConnection connection = new MySqlConnection(mysqlConnStr))
                {
                    dtSave.TableName = tableName;
                    connection.Open();
                    var bulk = new BulkOperation(connection);
                    bulk.AutoMapKeyName = "HotelId";
                    bulk.BulkInsert(dtSave);
                    //bulk.BulkUpdate(dt);
                    //bulk.BulkDelete(dt);
                    //bulk.BulkMerge(dt);
                    //bulk.BulkSaveChanges(ds);
                    //bulk.BulkSynchronize(dt);
                }
            }
            catch (Exception xe)
            {
                _logger.Error(xe.Message, xe);
            }
        }

        /// <summary>
        /// 美团普票ID导入
        /// </summary>
        public void SaveMeituanId()
        {
            try
            {
                var dtSaveMt = DbHelperSQL.Query("SELECT * FROM meituanhotelinfo_id WHERE 1=0").Tables[0];
                var mtIds = DbHelperMySQL.Query("SELECT HotelId FROM meituanhotelids", "MysqlConn", "meituanhoteldata").Tables[0];

                if (mtIds.Rows.Count > 0)
                {
                    foreach (DataRow dr in mtIds.Rows)
                    {
                        var newRow = dtSaveMt.NewRow();
                        newRow["HotelId"] = dr["HotelId"];
                        dtSaveMt.Rows.Add(newRow);
                    }

                    DbHelperSQL.ExecuteSql("TRUNCATE TABLE meituanhotelinfo_id");
                    SaveDataTableSqlServer(dtSaveMt, "meituanhotelinfo_id", "HotelId");
                }

                _logger.Info("SaveMeituanId OK!");
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        /// <summary>
        /// 美团专票ID导入
        /// </summary>
        public void SaveMeituanId_ZP()
        {
            try
            {
                var dtSaveMt = DbHelperSQL.Query("SELECT * FROM meituanhotelinfo_id_z WHERE 1=0").Tables[0];
                var mtIds = DbHelperMySQL.Query("SELECT HotelId FROM meituanhotelids", "MysqlConn", "meituanhoteldata_zp").Tables[0];

                if (mtIds.Rows.Count > 0)
                {
                    foreach (DataRow dr in mtIds.Rows)
                    {
                        var newRow = dtSaveMt.NewRow();
                        newRow["HotelId"] = dr["HotelId"];
                        dtSaveMt.Rows.Add(newRow);
                    }

                    DbHelperSQL.ExecuteSql("TRUNCATE TABLE meituanhotelinfo_id_z");
                    SaveDataTableSqlServer(dtSaveMt, "meituanhotelinfo_id_z", "HotelId");
                }

                _logger.Info("SaveMeituanId_ZP OK!");
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        public void UpdateHotelInfoBat()
        {
            try
            {
                var dtNewInfo = DbHelperSQL.Query(@"SELECT [Id]
                                            ,[Name]
                                            ,[StarRating]
                                            ,[Address]
                                            ,[EstablishmentDate]
                                            ,[RenovationDate]
                                            ,[NumberOfRooms]
                                            ,[FloorHeight]
                                            ,[Phone]
                                            ,[Fax]
                                            ,[Introduction]
                                            ,[Longitude]
                                            ,[Latitude]
                                            ,[CheckInTime]
                                            ,[CheckOutTime]
                                            ,[BusinessDistrict]
                                            ,[Facilities]
                                            ,[Keywords]
                                            ,[ThumbImageUrl]
                                            ,[StarCategory]
                                            ,[HotelFacilities],[SupplierId]
                                            ,[TotalScore], GETDATE() AS LastModificationTime, 'MtRepalce' AS LastModificationUserName
                                        FROM [HotelBicInfo_Meituan]").Tables[0];

                if (dtNewInfo != null && dtNewInfo.Rows.Count > 0)
                {
                    UptDataTableSqlServer(dtNewInfo, "HotelBasicInfoes");

                    _logger.Info("Finish UpdateHotelInfoBat");
                }
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        #region 美团唯一映射酒店数据更新

        /// <summary>
        /// 美团唯一映射酒店数据更新处理
        /// </summary>
        public void OnlyMeituanInfoUpt()
        {
            //先清空表
            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappings_MeituanOnly");
            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelBicInfo_Meituan");

            //重新插入数据
            var strSql = @"insert into HotelMappings_MeituanOnly
                select * from [dbo].[HotelMappings] 
                where supplierid='B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC' and hotelid in(
                select hotelid from [dbo].[HotelMappings] 
                where IsDeleted=0 
                group by hotelid having count(1)=1)";
            DbHelperSQL.ExecuteSql(strSql);

            //按城市处理
            var dtProv = DbHelperSQL.Query("SELECT Id,Name FROM Provinces").Tables[0];

            if (dtProv != null && dtProv.Rows.Count > 0)
            {
                foreach (DataRow drProv in dtProv.Rows)
                {
                    _logger.InfoFormat($">>>>开始处理省份：{drProv["Name"].ToString()}");

                    var dtCity = DbHelperSQL.Query($"SELECT Id,Name FROM Cities WHERE Isdeleted =0 AND ProvinceId='{drProv["Id"].ToString()}'").Tables[0];

                    if (dtCity != null && dtCity.Rows.Count > 0)
                    {
                        foreach (DataRow drCity in dtCity.Rows)
                        {
                            _logger.InfoFormat($"---->>开始处理城市：{drCity["Name"].ToString()}");

                            var cityId = Guid.Parse(drCity["Id"].ToString());
                            OnlyMeituanInfoUptByCity(cityId);

                            _logger.InfoFormat($"---->>城市处理完成：{drCity["Name"].ToString()}");
                        }
                    }

                    _logger.InfoFormat($">>>>省份：{drProv["Name"].ToString()} 结束>>>>");
                }
            }
        }

        /// <summary>
        /// 美团唯一映射酒店基础信息更新
        /// </summary>
        /// <param name="cityId"></param>
        public void OnlyMeituanInfoUptByCity(Guid cityId)
        {
            var strSql = $@"SELECT b.[Id], m.SupplierHotelId
                      ,[Name]
                      ,[Address]
                      ,[Phone]
                      ,[Longitude]
                      ,[Latitude]
                  FROM [HotelBasicInfoes] b,HotelMappings_MeituanOnly m
                  where b.CityId='{cityId}' and m.HotelId=b.Id and b.IsDeleted=0";

            try
            {
                var dtFjHotel = DbHelperSQL.Query(strSql).Tables[0];

                var dtMeituanHotel = DbHelperMySQL
                    .Query($@"SELECT HotelId,HotelName,HotelDescript,AmapLatitude,AmapLongitude,Address,BusinessDistrict,AvgScore,FloorNum,Telephone,FrontImage,OpenYear,RenovationYear,RoomQuantity,StarRating,CheckInFrom,CheckOutTo,FanjiaFacilities,FanjiaKeywords,LocationId
                        FROM meituanhotelinfo WHERE FanjiaCityId='{cityId}'", "MysqlConn", "meituanhoteldata").Tables[0];

                if (dtMeituanHotel != null && dtMeituanHotel.Rows.Count > 0)
                {
                    if (dtFjHotel.Rows.Count > 0)
                    {
                        var dtHotelNew = DbHelperSQL.Query("SELECT * FROM HotelBicInfo_Meituan WHERE 1=0").Tables[0];

                        var dicHotelId = new Dictionary<Guid, int>();

                        foreach (DataRow dr in dtFjHotel.Rows)
                        {
                            var hotelId = Guid.Parse(dr["Id"].ToString());
                            if (dicHotelId.ContainsKey(hotelId))
                                continue;

                            var supplierHotelId = dr["SupplierHotelId"].ToString();

                            var row = dtMeituanHotel.Select("HotelId=" + supplierHotelId);

                            OnlyRepalceInfo(row, dr, dtHotelNew, cityId);

                            dicHotelId.Add(hotelId, 1);
                        }

                        if (dtHotelNew.Rows.Count > 0)
                        {
                            SaveDataTableSqlServer(dtHotelNew, "HotelBicInfo_Meituan");
                        }
                    }
                }

                _logger.Info("OK!");
            }
            catch (Exception e)
            {
                _logger.Error(e.Message, e);
            }
        }

        #endregion
    }
}
